import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import matplotlib

# 设置全局字体（支持中文）
matplotlib.rcParams['font.sans-serif'] = ['SimHei']
matplotlib.rcParams['axes.unicode_minus'] = False

# 读取 Excel 文件
file_path = "表1.xlsx"
df = pd.read_excel(file_path, sheet_name="Sheet1")

# 剔除 CaO 中缺失值的行，并创建副本
df_cleaned = df.dropna(subset=['CaO']).copy()

# 计算 Na2O 的均值
mean_Na2O = df_cleaned['Na2O'].mean()

# 用 Na2O 的均值填充缺失值
df_cleaned = df_cleaned.assign(Na2O=df_cleaned['Na2O'].fillna(mean_Na2O))

# 保存清理后的数据
df_cleaned.to_excel("cleaned_data.xlsx", index=False)

# 读取清理后的数据
df_cleaned = pd.read_excel("cleaned_data.xlsx")

# 创建画布
plt.figure(figsize=(15, 5))

# === 1. 绘制 CaO 与 Na2O 的散点图 ===
plt.subplot(1, 3, 1)
sns.scatterplot(x=df_cleaned['CaO'], y=df_cleaned['Na2O'], alpha=0.7)
sns.regplot(x=df_cleaned['CaO'], y=df_cleaned['Na2O'], scatter=False, color='red')
plt.title("CaO 与 Na2O 的散点图")
plt.xlabel("CaO 含量")
plt.ylabel("Na2O 含量")
plt.grid(alpha=0.3)

# === 2. 绘制 CaO 直方图 ===
plt.subplot(1, 3, 2)
sns.histplot(df_cleaned['CaO'], bins=20, kde=True, color="blue", alpha=0.7)
plt.title("CaO 含量分布")
plt.xlabel("CaO 含量")
plt.ylabel("频数")
plt.grid(alpha=0.3)

# === 3. 绘制 Na2O 直方图 ===
plt.subplot(1, 3, 3)
sns.histplot(df_cleaned['Na2O'], bins=20, kde=True, color="green", alpha=0.7)
plt.title("Na2O 含量分布")
plt.xlabel("Na2O 含量")
plt.ylabel("频数")
plt.grid(alpha=0.3)

# 调整布局
plt.tight_layout()
plt.show()

